﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Data;

namespace Agresso.Interface.Layer.Service
{
    public class ud_voyageimp_Service
    {
        DataBaseConnectivity m_dataBaseConectivity;
        public ud_voyageimp_Service()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
        }
        public void Insert(ud_voyageimp udv)
        {
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(ud_voyageimpSQL.Insert(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = udv.client;
                cmd.Parameters.Add("@attribute_id", SqlDbType.NVarChar).Value = udv.attribute_id;
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = udv.dim_value;
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = udv.description;
                cmd.Parameters.Add("@rel_value", SqlDbType.NVarChar).Value = udv.rel_value;
                cmd.Parameters.Add("@voy_cpdate", SqlDbType.DateTime).Value = udv.voy_cpdate;
                cmd.Parameters.Add("@voy_actual_voy_start", SqlDbType.DateTime).Value = udv.voy_actual_voy_start;
                cmd.Parameters.Add("@voy_actual_voy_end", SqlDbType.DateTime).Value = udv.voy_actual_voy_end;
                cmd.Parameters.Add("@voy_total_actual_duration", SqlDbType.Decimal).Value = udv.voy_total_actual_duration;
                cmd.Parameters.Add("@voy_offhire_duration", SqlDbType.Decimal).Value = udv.voy_offhire_duration;
                cmd.Parameters.Add("@voy_cargo_type", SqlDbType.NVarChar).Value = udv.voy_cargo_type;
                cmd.Parameters.Add("@voy_cargo_quantity", SqlDbType.Decimal).Value = udv.voy_cargo_quantity;
                cmd.Parameters.Add("@voy_fixture_number", SqlDbType.NVarChar).Value = udv.voy_fixture_number;
                cmd.Parameters.Add("@voy_freight_rate_per_day", SqlDbType.NVarChar).Value = udv.voy_freight_rate_per_day;
                cmd.Parameters.Add("@voy_tc_rate_per_day", SqlDbType.NVarChar).Value = udv.voy_tc_rate_per_day;
                cmd.Parameters.Add("@voy_type_of_vc", SqlDbType.NVarChar).Value = udv.voy_type_of_vc;
                cmd.Parameters.Add("@voy_head_fixture_ref", SqlDbType.NVarChar).Value = udv.voy_head_fixture_ref;
                cmd.Parameters.Add("@processed_status", SqlDbType.NVarChar).Value = udv.processed_status;
                cmd.Parameters.Add("@processed_date", SqlDbType.DateTime).Value = udv.processed_date;
                cmd.Parameters.Add("@voy_cargo_quantity1", SqlDbType.Float).Value = udv.voy_cargo_quantity1;
                cmd.Parameters.Add("@voy_charterer", SqlDbType.NVarChar).Value = udv.voy_charterer;
                cmd.Parameters.Add("@voy_actual_voy_start_time", SqlDbType.NVarChar).Value = udv.voy_actual_voy_start.ToString("HH:mm");
                cmd.Parameters.Add("@voy_actual_voy_end_time", SqlDbType.NVarChar).Value = udv.voy_actual_voy_end.ToString("HH:mm");
                cmd.Parameters.Add("@voy_lumpsum_freight", SqlDbType.NVarChar).Value = udv.voy_lumpsum_freight;

                LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                cmd.CommandText = ud_voyageimpSQL.GetMaxrecidud_voyageimpSQL();
                cmd.Parameters.Clear();
                udv.recid = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }
        public void InsertIntoMiddleLayer(ud_voyageimp udv)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(ud_voyageimpSQL.InsertIntoMiddleLayer(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@recid", SqlDbType.Int).Value = udv.recid;
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = udv.client;
                cmd.Parameters.Add("@attribute_id", SqlDbType.NVarChar).Value = udv.attribute_id;
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = udv.dim_value;
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = udv.description;
                cmd.Parameters.Add("@rel_value", SqlDbType.NVarChar).Value = udv.rel_value;
                cmd.Parameters.Add("@voy_cpdate", SqlDbType.DateTime).Value = udv.voy_cpdate;
                cmd.Parameters.Add("@voy_actual_voy_start", SqlDbType.DateTime).Value = udv.voy_actual_voy_start;
                cmd.Parameters.Add("@voy_actual_voy_end", SqlDbType.DateTime).Value = udv.voy_actual_voy_end;
                cmd.Parameters.Add("@voy_total_actual_duration", SqlDbType.Decimal).Value = udv.voy_total_actual_duration;
                cmd.Parameters.Add("@voy_offhire_duration", SqlDbType.Decimal).Value = udv.voy_offhire_duration;
                cmd.Parameters.Add("@voy_cargo_type", SqlDbType.NVarChar).Value = udv.voy_cargo_type;
                cmd.Parameters.Add("@voy_cargo_quantity", SqlDbType.Decimal).Value = udv.voy_cargo_quantity;
                cmd.Parameters.Add("@voy_fixture_number", SqlDbType.NVarChar).Value = udv.voy_fixture_number;
                cmd.Parameters.Add("@voy_freight_rate_per_day", SqlDbType.NVarChar).Value = udv.voy_freight_rate_per_day;
                cmd.Parameters.Add("@voy_tc_rate_per_day", SqlDbType.NVarChar).Value = udv.voy_tc_rate_per_day;
                cmd.Parameters.Add("@voy_type_of_vc", SqlDbType.NVarChar).Value = udv.voy_type_of_vc;
                cmd.Parameters.Add("@voy_head_fixture_ref", SqlDbType.NVarChar).Value = udv.voy_head_fixture_ref;
                cmd.Parameters.Add("@processed_status", SqlDbType.NVarChar).Value = udv.processed_status;
                cmd.Parameters.Add("@processed_date", SqlDbType.DateTime).Value = udv.processed_date;
                cmd.Parameters.Add("@voy_cargo_quantity1", SqlDbType.Float).Value = udv.voy_cargo_quantity1;
                cmd.Parameters.Add("@voy_charterer", SqlDbType.NVarChar).Value = udv.voy_charterer;
                cmd.Parameters.Add("@voy_actual_voy_start_time", SqlDbType.NVarChar).Value = udv.voy_actual_voy_start.ToString("HH:mm");
                cmd.Parameters.Add("@voy_actual_voy_end_time", SqlDbType.NVarChar).Value = udv.voy_actual_voy_end.ToString("HH:mm");
                cmd.Parameters.Add("@voy_lumpsum_freight", SqlDbType.NVarChar).Value = udv.voy_lumpsum_freight;
                cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        //public bool IsRegisteredVoyage(string dimVal, string voyage)
        //{
        //    bool result = false;
        //    try
        //    {
        //        m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
        //        SqlCommand cmd = new SqlCommand(ud_voyageimpSQL.IsRegisteredVoyage(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
        //        cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dimVal + "-V" + voyage.PadLeft(3, '0');
        //        int count = Convert.ToInt16(cmd.ExecuteScalar());
        //        result = count > 0;
        //        return result;
        //    }
        //    catch (Exception x)
        //    {
        //        LogService.WriteLogException(x);
        //        throw x;
        //    }
        //    finally
        //    {
        //        m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
        //    }
        //}

        public bool IsRegisteredVoyage(string dimVal, string voyage)
        {
            bool result = false;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(ud_voyageimpSQL.IsRegisteredVoyage(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dimVal + "-V" + voyage.PadLeft(3, '0');
                int count = Convert.ToInt16(cmd.ExecuteScalar());
                result = count > 0;
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }

        public afxvoyage GetAFXVOYAGEByDimValue(string dimVal, string voyage)
        {
            afxvoyage afxvoyage = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(ud_voyageimpSQL.GetAFXVOYAGEByDimValue(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dimVal + "-V" + voyage.PadLeft(3, '0');
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    afxvoyage = new afxvoyage(rd);
                }
                return afxvoyage;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }


        internal ud_voyageimp Find(ud_voyageimp udv)
        {
            ud_voyageimp result = null;
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(ud_voyageimpSQL.Find(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = udv.client;
                cmd.Parameters.Add("@attribute_id", SqlDbType.NVarChar).Value = udv.attribute_id;
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = udv.dim_value;
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = udv.description;
                cmd.Parameters.Add("@rel_value", SqlDbType.NVarChar).Value = udv.rel_value;
                cmd.Parameters.Add("@voy_cpdate", SqlDbType.DateTime).Value = udv.voy_cpdate;
                cmd.Parameters.Add("@voy_actual_voy_start", SqlDbType.DateTime).Value = udv.voy_actual_voy_start;
                cmd.Parameters.Add("@voy_actual_voy_end", SqlDbType.DateTime).Value = udv.voy_actual_voy_end;
                cmd.Parameters.Add("@voy_total_actual_duration", SqlDbType.Decimal).Value = udv.voy_total_actual_duration;
                cmd.Parameters.Add("@voy_offhire_duration", SqlDbType.Decimal).Value = udv.voy_offhire_duration;
                cmd.Parameters.Add("@voy_cargo_type", SqlDbType.NVarChar).Value = udv.voy_cargo_type;
                cmd.Parameters.Add("@voy_cargo_quantity", SqlDbType.Decimal).Value = udv.voy_cargo_quantity;
                cmd.Parameters.Add("@voy_fixture_number", SqlDbType.NVarChar).Value = udv.voy_fixture_number;
                cmd.Parameters.Add("@voy_freight_rate_per_day", SqlDbType.NVarChar).Value = udv.voy_freight_rate_per_day;
                cmd.Parameters.Add("@voy_tc_rate_per_day", SqlDbType.NVarChar).Value = udv.voy_tc_rate_per_day;
                cmd.Parameters.Add("@voy_type_of_vc", SqlDbType.NVarChar).Value = udv.voy_type_of_vc;
                cmd.Parameters.Add("@voy_head_fixture_ref", SqlDbType.NVarChar).Value = udv.voy_head_fixture_ref;
                cmd.Parameters.Add("@processed_status", SqlDbType.NVarChar).Value = udv.processed_status;
                cmd.Parameters.Add("@processed_date", SqlDbType.DateTime).Value = udv.processed_date;
                cmd.Parameters.Add("@voy_actual_voy_start_time", SqlDbType.NVarChar).Value = udv.voy_actual_voy_start.ToString("hh:MM");
                cmd.Parameters.Add("@voy_actual_voy_end_time", SqlDbType.NVarChar).Value = udv.voy_actual_voy_end.ToString("hh:MM");
                cmd.Parameters.Add("@voy_lumpsum_freight", SqlDbType.NVarChar).Value = udv.voy_lumpsum_freight;
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new ud_voyageimp(rd);
                }
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }

        internal void Update(ud_voyageimp udv)
        {
            try
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(ud_voyageimpSQL.Update(), m_dataBaseConectivity.AgrTempConnectionSQLConnection);
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = udv.client;
                cmd.Parameters.Add("@attribute_id", SqlDbType.NVarChar).Value = udv.attribute_id;
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = udv.dim_value;
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = udv.description;
                cmd.Parameters.Add("@rel_value", SqlDbType.NVarChar).Value = udv.rel_value;
                cmd.Parameters.Add("@voy_cpdate", SqlDbType.DateTime).Value = udv.voy_cpdate;
                cmd.Parameters.Add("@voy_actual_voy_start", SqlDbType.DateTime).Value = udv.voy_actual_voy_start;
                cmd.Parameters.Add("@voy_actual_voy_end", SqlDbType.DateTime).Value = udv.voy_actual_voy_end;
                cmd.Parameters.Add("@voy_total_actual_duration", SqlDbType.Decimal).Value = udv.voy_total_actual_duration;
                cmd.Parameters.Add("@voy_offhire_duration", SqlDbType.Decimal).Value = udv.voy_offhire_duration;
                cmd.Parameters.Add("@voy_cargo_type", SqlDbType.NVarChar).Value = udv.voy_cargo_type;
                cmd.Parameters.Add("@voy_cargo_quantity", SqlDbType.Decimal).Value = udv.voy_cargo_quantity;
                cmd.Parameters.Add("@voy_fixture_number", SqlDbType.NVarChar).Value = udv.voy_fixture_number;
                cmd.Parameters.Add("@voy_freight_rate_per_day", SqlDbType.NVarChar).Value = udv.voy_freight_rate_per_day;
                cmd.Parameters.Add("@voy_tc_rate_per_day", SqlDbType.NVarChar).Value = udv.voy_tc_rate_per_day;
                cmd.Parameters.Add("@voy_type_of_vc", SqlDbType.NVarChar).Value = udv.voy_type_of_vc;
                cmd.Parameters.Add("@voy_head_fixture_ref", SqlDbType.NVarChar).Value = udv.voy_head_fixture_ref;
                cmd.Parameters.Add("@processed_status", SqlDbType.NVarChar).Value = udv.processed_status;
                cmd.Parameters.Add("@processed_date", SqlDbType.DateTime).Value = udv.processed_date;

                cmd.Parameters.Add("@voy_actual_voy_start_time", SqlDbType.NVarChar).Value = udv.voy_actual_voy_start.ToString("hh:MM");
                cmd.Parameters.Add("@voy_actual_voy_end_time", SqlDbType.NVarChar).Value = udv.voy_actual_voy_end.ToString("hh:MM");
                cmd.Parameters.Add("@voy_lumpsum_freight", SqlDbType.NVarChar).Value = "0";

                cmd.Parameters.Add("@recid", SqlDbType.NVarChar).Value = udv.recid;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
            }
        }
    }
}
